package com.cqut.servlet;

import com.alibaba.fastjson.JSON;
import com.cqut.entity.student;
import com.cqut.utils.DBUtils;
import com.cqut.utils.ResultData;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class ServletStudent extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.setContentType("text/html;charset=utf-8");
        req.setCharacterEncoding("utf-8");

        String action = req.getParameter("action") == null ? "" : req.getParameter("action");
        switch (action) {
            case"getdglist":
                getDglist(req,resp);
                break;
            case"add":
                Add(req,resp);
                break;
            case"exists":
                Exists(req,resp);
                break;
            case"delete":
                Delete(req,resp);
                break;
            case"getone":
                getOne(req,resp);
                break;
            case"update":
                Update(req,resp);
                break;
            case"search":
                getSearch(req,resp);
                break;
            default:
                break;
        }
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req, resp);
    }

    //分页
    private void getDglist(HttpServletRequest req, HttpServletResponse resp) {

        try {
            //获取参数
            String pageIndex = req.getParameter("page") == null ? "1" : req.getParameter("page");
            String pageSize = req.getParameter("rows") == null ? "10" : req.getParameter("rows");

            // 将字符串转换为整数，并处理可能的异常
            int currentPage = Integer.parseInt(pageIndex);
            int pageSizeInt = Integer.parseInt(pageSize);

            // 确保页数和每页大小都是合理的值
            if (currentPage < 1) currentPage = 1;
            if (pageSizeInt < 1) pageSizeInt = 10;


            PrintWriter out = resp.getWriter();
            HashMap<String, Object> map = new HashMap<>();
            // 获取总记录数
            String sqlcount = "select count(*) from student";
            int total = Integer.parseInt(DBUtils.QueryScalar(sqlcount).toString());
            map.put("total", total);
//            String sqllist ="SELECT * FROM student LIMIT 10";

            // 计算LIMIT子句的参数
            int offset = (currentPage - 1) * pageSizeInt;
//            String sqllist = "SELECT * FROM student LIMIT " + offset + ", " + pageSizeInt;
            String sqllist = "SELECT * FROM ( " +
                    "  SELECT (@row_number:=@row_number + 1) AS rownumber, tmp_table.* " +
                    "  FROM ( SELECT * FROM student ORDER BY sno ASC ) AS tmp_table, (SELECT @row_number:=0) AS t " +
                    ") AS tmp_table " +
                    "WHERE rownumber > " + offset + " " +
                    "LIMIT " + pageSize;

            //问题在这一句
            List<student> list = DBUtils.QueryBeanList(sqllist, student.class);

            map.put("rows", list);
            out.write(JSON.toJSONString(map));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //搜索功能
    private void getSearch(HttpServletRequest req, HttpServletResponse resp) {

        try {

            PrintWriter out = resp.getWriter();
            String sno = req.getParameter("key1");

            HashMap<String, Object> map = new HashMap<>();

            int total=10;
            map.put("total", total);

            String sql = "select * from student where sno = ?";
            student student = DBUtils.QueryBean(sql, student.class, sno);

            if (student == null){
                out.write("404");
            }else {
                //rows属性的值似乎是一个对象，而不是一个对象数组。DataGrid通常期望rows是一个数组，每个数组元素代表一行数据。
                //需要搜索是数组，所以用list
                List<student> students = new ArrayList<>();
                if (student != null) {
                    students.add(student);
                }

                map.put("rows", students);
                String jsonString = JSON.toJSONString(map);

                out.write(jsonString);
            }
        }
        catch (Exception e){
            e.printStackTrace();
        }

    }

    //添加数据
    private void Add(HttpServletRequest req, HttpServletResponse resp) throws IOException {
        try{
            PrintWriter out = resp.getWriter();
            String sno = req.getParameter("sno");
            String sname = req.getParameter("sname");
            String sex= req.getParameter("sex");
            Integer year = Integer.valueOf(req.getParameter("year"));
            String birth= req.getParameter("birth");
            String political= req.getParameter("political");
            String snotes= req.getParameter("snotes");
            String address= req.getParameter("address");
            String zip= req.getParameter("zip");
            String system= req.getParameter("system");
            String major= req.getParameter("major");


            String sql = "insert into student (sno, sname, sex, year,birth,political,snotes,address,zip,system,major) values(?,?,?,?,?,?,?,?,?,?,?)";
            if(DBUtils.Update(sql,sno,sname,sex,year,birth,political,snotes,address,zip,system,major) > 0){
                out.write(JSON.toJSONString(new ResultData(String.valueOf(resp.getStatus()),"保存成功")));
            }
            else{
                out.write(JSON.toJSONString(new ResultData("501","保存失败")));
            }

        }catch (Exception e) {
            resp.getWriter().write(JSON.toJSONString(new ResultData("501","数据有误")));
        }
    }

    //判断编码是否存在

    private void Exists(HttpServletRequest req, HttpServletResponse resp) {
        try{
            PrintWriter out = resp.getWriter();
            String sno = req.getParameter("sno");
            String sql = "select count(*) from student where sno = ?";
            int count = Integer.parseInt(DBUtils.QueryScalar(sql,sno).toString());
            if(count > 0){
//                out.write(JSON.toJSONString(new ResultData("200","数据已经存在")));
                out.write("false");
            }
            else
                out.write("true");
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    //删除行
    private void Delete(HttpServletRequest req, HttpServletResponse resp) {
        try{
            PrintWriter out = resp.getWriter();
            String sno = req.getParameter("sno");
            String sql = "delete from student where sno = ?";
            if(DBUtils.Update(sql,sno) >0){
                out.write(JSON.toJSONString(new ResultData("200","删除成功")));
            }
            else{
                out.write(JSON.toJSONString(new ResultData("500","删除失败")));
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取选择行数据
    private void getOne(HttpServletRequest req, HttpServletResponse resp) {
        try{
            PrintWriter out = resp.getWriter();
            String sno = req.getParameter("sno");
            String sql = "select * from student where sno = ?";
            student student = DBUtils.QueryBean(sql, student.class,sno);
            ResultData rd = new ResultData();
            if(student != null){
                rd.setCode(String.valueOf(resp.getStatus()));
                rd.setMsg("获取成功");
                rd.setData(student);
                out.write(JSON.toJSONString(rd));
            }
            else{
                rd.setCode("501");
                rd.setMsg("获取失败");
                out.write(JSON.toJSONString(rd));
            }

        }catch (Exception e) {
            e.printStackTrace();
        }
    }


    //修改行数据
    private void Update(HttpServletRequest req, HttpServletResponse resp) throws IOException {
        try{
            PrintWriter out = resp.getWriter();
            String sno = req.getParameter("sno");
            String sname = req.getParameter("sname");
            String sex= req.getParameter("sex");
            Integer year = Integer.valueOf(req.getParameter("year"));
            String birth= req.getParameter("birth");
            String political= req.getParameter("political");
            String snotes= req.getParameter("snotes");
            String address= req.getParameter("address");
            String zip= req.getParameter("zip");
            String system= req.getParameter("system");
            String major= req.getParameter("major");

            String sql = "update student set sname= ? ,sex =? ,year =? ,birth =? ,political =?, snotes=?, address=?, zip=?, system=?, major=? where sno = ?";
            if(DBUtils.Update(sql,sname,sex,year,birth,political,snotes,address,zip,system,major,sno) > 0){
                out.write(JSON.toJSONString(new ResultData(String.valueOf(resp.getStatus()),"保存成功")));
            }
            else{
                out.write(JSON.toJSONString(new ResultData("501","保存失败")));
            }

        }catch (Exception e) {
            resp.getWriter().write(JSON.toJSONString(new ResultData("501","数据有误")));
        }
    }

}
